package com.amt.olcacon.utils;

import com.amt.olcacon.bo.FileData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

@Component
@Slf4j
public class ExcelParserTwoTablesAllSheet {

    private final MinioUtil minioUtil ;

    // 字段映射表（中文 -> 英文）
    private static final Map<String, String> PRODUCT_FIELD_MAP = new HashMap<>();
    private static final Map<String, String> EXCHANGE_FIELD_MAP = new HashMap<>();

    static {
        PRODUCT_FIELD_MAP.put("*工序名称", "process_name");
        PRODUCT_FIELD_MAP.put("所属产品", "product_name");
        PRODUCT_FIELD_MAP.put("地理位置", "location");
        PRODUCT_FIELD_MAP.put("描述", "description");

        EXCHANGE_FIELD_MAP.put("*输入输出类型", "io_type");
        EXCHANGE_FIELD_MAP.put("*来源", "source");
        EXCHANGE_FIELD_MAP.put("*名称", "name");
        EXCHANGE_FIELD_MAP.put("*单位组（英文）", "unit_group");
        EXCHANGE_FIELD_MAP.put("*分类", "category");
        EXCHANGE_FIELD_MAP.put("编号", "number");
        EXCHANGE_FIELD_MAP.put("地理位置（code）", "location_code");
        EXCHANGE_FIELD_MAP.put("环境流分类", "environmental_flow_category");
        EXCHANGE_FIELD_MAP.put("*类型", "type");
        EXCHANGE_FIELD_MAP.put("*分配系数（0～1）", "allocation_factor");
        EXCHANGE_FIELD_MAP.put("*上游数据类型", "upstream_type");
        EXCHANGE_FIELD_MAP.put("*背景数据名称", "background_name");
        EXCHANGE_FIELD_MAP.put("*国家/地区（英文简写）", "country_code");
        EXCHANGE_FIELD_MAP.put("*工序名称", "process_name");
        EXCHANGE_FIELD_MAP.put("*所属产品", "product_name");
        EXCHANGE_FIELD_MAP.put("填报值", "enter_value");
    }

    public ExcelParserTwoTablesAllSheet(MinioUtil minioUtil) {
        this.minioUtil = minioUtil;
    }

    public List<FileData> getFileData(String filePath) throws IOException {

        List<FileData> fileDataList = new ArrayList<>();
        String minioFilePath = RegexUrlConverter.convertUrlToFilename(filePath);
        log.info("解析出的路径是{} ", minioFilePath);

        InputStream stream = minioUtil.getFileStream(minioFilePath);
//        InputStream stream =  new FileInputStream("filePath");


        
        try (Workbook workbook = new XSSFWorkbook(stream)) {
            // 遍历所有sheet
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                String sheetName = sheet.getSheetName();
                log.info("开始解析sheet: {}", sheetName);
                
                // 为每个sheet创建一个FileData对象
                FileData fileData = new FileData();
                
                // 解析产品表（第 1 行字段 + 第 2 行数据）
                Row productHeader = sheet.getRow(0);
                Row productData = sheet.getRow(1);
                if (productHeader != null && productData != null) {
                    Map<String, String> product = parseRow(productHeader, productData, PRODUCT_FIELD_MAP);
                    System.out.println("Sheet " + sheetName + " Product Table:");
                    System.out.println(product);
                    fileData.setProduct(product);
                } else {
                    log.warn("Sheet {} 缺少产品表数据", sheetName);
                    continue; // 跳过没有产品表数据的sheet
                }

                // 解析交换表（第 5 行字段 + 第 6 行及之后数据）
                Row exchangeHeader = sheet.getRow(4);
                List<Map<String, String>> exchanges = new ArrayList<>();
                if (exchangeHeader != null) {
                    for (int i = 5; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;
                        Map<String, String> exchange = parseRow(exchangeHeader, row, EXCHANGE_FIELD_MAP);
                        exchanges.add(exchange);
                    }
                    fileData.setExchange(exchanges);
                    System.out.println("Sheet " + sheetName + " Exchange Table:");
                    exchanges.forEach(System.out::println);
                } else {
                    log.warn("Sheet {} 缺少交换表表头", sheetName);
                }
                
                // 将当前sheet的数据添加到结果列表
                fileDataList.add(fileData);
                log.info("成功解析sheet: {}, 包含 {} 条交换记录", sheetName, exchanges.size());
            }
            
            log.info("Excel文件解析完成，共解析 {} 个sheet", fileDataList.size());
            return fileDataList;
        }
    }

    // 工具方法：解析一行
    private static Map<String, String> parseRow(Row header, Row data, Map<String, String> fieldMap) {
        Map<String, String> result = new LinkedHashMap<>();
        for (int i = 0; i < header.getLastCellNum(); i++) {
            Cell headerCell = header.getCell(i);
            Cell dataCell = data.getCell(i);

            if (headerCell == null) continue;
            String headerName = headerCell.getStringCellValue().trim();
            String engName = fieldMap.getOrDefault(headerName, headerName);

            String value = "";
            if (dataCell != null) {
                dataCell.setCellType(CellType.STRING);
                value = dataCell.getStringCellValue().trim();
            }
            result.put(engName, value);
        }
        return result;
    }

//    public static void main(String[] args) {
//        try {
//            List<FileData> fileDataList = this.getFileData("/Users/lixk/Downloads/底层工序导入第一层（sheet名称勿动）.xlsx");
//            System.out.println(fileDataList);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }
}